# Pull and save LTV and size data at MBS level from the eMBS quartile data (20190506 version)

library(tidyverse)
library(knitr)
library(haven)

rootdir <- file.path(dirname(getwd()))
scratchdir <- file.path(rootdir, "data", "scratch_data")
cachedir <- file.path(scratchdir, "cached_data")
embsdir <- file.path(scratchdir, "embs")
rawdir <- file.path(rootdir, "data", "scratch_data", "raw_data")

raw_embs <- read_csv(file.path(rawdir, "embs.csv"),
                 col_types = "cccdTiTcicciccdii")
raw_embs <- raw_embs %>%
  filter(embs_productsuptype == "POOL") %>%
  select(cusip_id, issue_id)

# The new embs quartile data
embs_quartile <- read_csv(file.path(embsdir, "embs_quartile_20190506.csv"),
                          col_types = "iTccd")
# There is one weighted_avg == 999 for OLTV. Delete the observation
embs_quartile <- embs_quartile %>%
  filter(weighted_avg != 999 | field_id != "OLTV")

embs_quartile <- embs_quartile %>%
  group_by(issue_id, field_id) %>%
  arrange(orig_updated_ind) %>%
  filter(row_number() == 1) %>%
  ungroup()

quartile_wide <- embs_quartile %>%
  select(-effective_dt, -orig_updated_ind) %>%
  spread(field_id, weighted_avg)

embs_w_quartile <- left_join(raw_embs, quartile_wide, by = "issue_id") %>%
  select(-issue_id)

saveRDS(embs_w_quartile, file.path(cachedir, "20190506_LTV_size_from_embs_quartile.RDS"))
write_csv(embs_w_quartile, file.path(cachedir, "20190506_LTV_size_from_embs_quartile.csv"))
